package com.wondersgroup.fjzlpt.console.dao.impl.ksxx;

import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.console.dao.inter.ksxx.KsxxDAO;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class KsxxDAOImpl extends BaseJTDAOImpl implements KsxxDAO {

    @Override
    @Autowired
    @Qualifier("TB_ZD_KSXX")
    public void setTb(TableBean tb) {
        super.setTb(tb);
    }

    @Override
    public JSONArray queryListByPtjgidAndJb(Map map) {
        StringBuilder sbud = new StringBuilder("select KSDM, KSMC, SJKSDM from ").append(this.tb.getTableName())
            .append(" where 1=1 and PTJGID=?");
        List list = new ArrayList();
        String ptjgid = (String)map.get("PTJGID");
        list.add(ptjgid);
        String jb = (String)map.get("JB");
        if(!StringUtils.isEmpty(jb)){
            sbud.append(" and JB=?");
            list.add(jb);
        }
        sbud.append(" order by KEYWORD");
        SQLBean sql = new SQLBean(sbud.toString(), list.toArray());
        return super.queryList(sql);
    }

    /**
     * 查询二级所有科室信息
     * @param
     * @return
     */
    public JSONArray querySecondJgKsxx(Map map){
        StringBuilder sbud = new StringBuilder(this.tb.getSqlQuery())
                .append(" where 1=1 and PTJGID=? and SJKSDM=?");
        String PTJGID = (String)map.get("PTJGID");
        String SJKSDM = (String)map.get("KSDM");
        SQLBean sb = new SQLBean(sbud.toString(), new Object[]{PTJGID, SJKSDM});
        return this.queryList(sb);
    }

    /**
     * 查询机构科室详细信息
     * @param map
     * @return
     */
    public JSONObject queryJgKsxxDetail(Map map){
        String PTJGID = (String)map.get("PTJGID");
        String KSDM = (String)map.get("KSDM");
        StringBuilder sbud = new StringBuilder(this.tb.getSqlQuery())
                .append(" where PTJGID=? and KSDM=?");
        SQLBean sb = new SQLBean(sbud.toString(), new Object[]{PTJGID, KSDM});
        return this.queryOne(sb);
    }

    /**
     * 分页查询机构科室信息
     * @param
     * @return
     */
    public JSONObject queryPageKsxx(Map map){
        StringBuilder sbud = new StringBuilder("select c.kssl , b.yljgdm, b.yljgmc, a.* \n")
                .append("from tb_zd_ksxx a left join tb_zd_yljgxx b on a.ptjgid=b.ptjgid \n")
                .append("left join (select sjksdm ksdm,ptjgid,count(1) kssl from tb_zd_ksxx \n" +
                        "where jb = '2' \n" +
                        "group by ptjgid,sjksdm ) c on a.ksdm = c.ksdm and a.ptjgid=c.ptjgid ")
                .append("where a.JB='1' \n");
        List list = new ArrayList();
        String value = (String)map.get("PTJGID");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and b.PTJGID=? \n");
            list.add(value);
        }else{
            value = (String)map.get("YLJGMC");
            if(!StringUtils.isEmpty(value)){
                sbud.append(" and b.YLJGMC like ? \n");
                list.add("%"+value+"%");
            }
        }
        value = (String)map.get("KSMC");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and a.KSMC like ? \n");
            list.add("%"+value+"%");
        }
        value = (String)map.get("XZQHDM");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and b.XZQHDM like ? \n");
            list.add(value+"%");
        }
        PageQuerySQLBean sb = checkAndCreatePageQuerySQLBean(map, sbud.toString(), "order by b.PTJGID, a.KSMC", list.toArray());
        return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
    }

    /**
     * 删除机构的科室和关联的二级科室
     * @param ptjgid
     * @param keyword
     */
    public void deleteOneAndTwo(String ptjgid, String keyword){
        StringBuilder sbud = new StringBuilder("delete from ").append(this.tb.getTableName())
                .append(" where 1=1 and PTJGID=? and KEYWORD like ?");
        SQLBean sb = new SQLBean(sbud.toString(), new Object[]{ptjgid, keyword + "%"});
        this.update(sb);
    }

    /**
	 * 查询一级科室信息与二级科室信息用于导出
	 * @param map
	 */
	@Override
	public JSONArray queryAllForExcel(Map map) {
		StringBuilder sbud = new StringBuilder("select c.yljgdm, c.yljgmc,a.KSMC YJKSMC,d.XMMC BZKSMC,a.SFTSKS YJSFTSKS,b.KSMC EJKSMC,b.KSDM EJKSDM,b.SFTSKS ERSFTSKS \n")
	        .append("from tb_zd_ksxx a left join tb_zd_ksxx b on a.KSDM = b.SJKSDM and a.PTJGID=b.PTJGID \n")
	        .append("left join tb_zd_yljgxx c on a.PTJGID=c.PTJGID \n")
		    .append("left join TB_ZY_BZKS d on a.PTKSDM=d.XMDM \n")
            .append("where a.JB='1' ");
		List list = new ArrayList();
		String value = (String)map.get("PTJGID");
		if(!StringUtils.isEmpty(value)){
		    sbud.append(" and a.PTJGID=? \n");
		    list.add(value);
		}else{
		    value = (String)map.get("YLJGMC");
		    if(!StringUtils.isEmpty(value)){
		        sbud.append(" and c.YLJGMC like ? \n");
		        list.add("%"+value+"%");
		    }
		}
		value = (String)map.get("KSMC");
		if(!StringUtils.isEmpty(value)){
		    sbud.append(" and a.KSMC like ? \n");
		    list.add("%"+value+"%");
		}
        value = (String)map.get("XZQHDM");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and c.XZQHDM like ? \n");
            list.add(value+"%");
        }
		SQLBean bean = new SQLBean(sbud.toString() + " order by a.PTJGID, a.KSMC", list.toArray());
		return super.queryList(bean);
	}
}
